''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者：cacho_37967865
#博客：https://blog.csdn.net/sinat_37967865
#文件：pymysqlModel.py
#日期：2018-10-22
#备注： sample来源：E:\YOUYI\BALANCE LIST
# S6240111G3-->GIL 2024-5-24 DHL LIST(1)-->G
# S6241208T1-->GIL 2022-12-16 dhl list-->T
# S7240108M1-->manuel balance list-->M1
# S7231204A1-->jordan fedex list + IAN LIST-->A
# S7231205S1-->VICTOR BALANCE LIST-->S
# S7230620D1-->DAVID'S LIST-->D 
# S7230705J1,S7230725C1,S7230720K1,S7230721L1 -->其他
2-C发票号 14-O-金额
对账单：1-B,11-L
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

import time
import xlrd
from xlutils.copy import copy
import os
import re
from homework.config import db,cursor

start_time = time.time()  #计算程序运行时间

month = '202501~03'
sunshine_bill = ['S625','S725']
end_name = ['G','T','R','M']
other_bill = ['718','318','444','639','601','TGZ','S30','S70','S40','S71','S73']

def createTable():
    sql1 = 'drop table if exists save_amount;'
    sql2 = 'create table save_amount(No INT NOT NULL AUTO_INCREMENT,billNo VARCHAR(50),amount FLOAT DEFAULT 0.0,PRIMARY KEY (No));'
    cursor.execute(sql1)
    cursor.execute(sql2)
    db.commit()

def deleteTable():
    sql1 = 'delete from save_amount_bak;'
    cursor.execute(sql1)
    db.commit()


# 获取货运对账文件中非sunshine账单的数据,拿（第二B）列运单号，运费记为0
def get_excel_data(xlsFile):
    data = xlrd.open_workbook(xlsFile)
    table = data.sheets()[0]
    nrows = table.nrows
    sqlData = []
    # 获取指定（第二B）列数据
    for i in range(1, nrows):
        billNo = table.cell_value(i, 1)
        try:
            billNo = int(billNo)
            billNo = str(billNo)

        except Exception as f:
            pass
            #print(str(f))

        row = []
        # 单号不为空，长度超过11，不含有字母M或G或T, 前面4位不在指定列表内
        if billNo != '' and (len(billNo)>12 or bool(re.search(r'[MGTR]', billNo))==False or (billNo[0:4].upper() not in sunshine_bill)):  # 前4位数
            row.append(billNo)
            row.append(0.0)
            #print("不处理的：",billNo)
        else:
            continue              # 不符合要求就跳过
        sqlData.append(row)
    #print(sqlData)
    return sqlData


# 客户的多个账单，（第三列C）列数据为运单号，第15列-->O为运费
def get_excels_data(folder):
    files = os.listdir(folder)
    xlsFiles = [f for f in files if f.endswith(".xls")]
    sqlDatas = []
    for xlsFile in xlsFiles:
        os.chdir(folder)  # 切换到操作目录
        print(xlsFile)
        data = xlrd.open_workbook(xlsFile)  # 打开excle文档读取数据
        table = data.sheets()[0]
        nrows = table.nrows
        # 获取指定（第三列C）列数据
        for i in range(1, nrows):
            billNo = table.cell_value(i, 2)
            try:
                billNo = str(billNo)
            except Exception as f:
                #pass
                print(str(f))

            amount = table.cell_value(i, 14)   # 14-->O SITUATION  12-M-AMOUNT
            row = []

            # 有发票号并且发票号前4个属于sunshine_bill列表
            if billNo != '' and (billNo[0:4].upper() in sunshine_bill):
                row.append(billNo)
            else:
                continue  # 不符合要求就跳过

            if isinstance(amount,float):
                row.append(amount)  # 有金额
            #elif amount == '' and table.cell_value(i, 12) !='':
                #row.append(table.cell_value(i, 12)) # 无金额并且amount那一列不为空
            else:
                row.append(0.0)
            sqlDatas.append(row)
    #print(sqlDatas)
    return sqlDatas


def data_to_mysql(billNo,amount):
    sql = "insert into save_amount(billNo,amount) values('%s','%f')" % (billNo,amount)
    try:
        # 使用 cursor() 方法创建一个游标对象 cursor
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        #print(str(e))
    else:
        db.commit()  # 事务提交
        #print('插入成功的单：',billNo)


# 将有重复运单号和对账金额为0的异常数据拿出来
def get_error_data():
    cursor.execute('select * from save_amount where billNo in (select billNo from save_amount group by billNo having count(billNo)>1);')
    cursor.execute("select * from save_amount where No in (select No from save_amount where amount =0 and SUBSTRING(billNo,1,3) in ('218','518','618','818'));")
    results = cursor.fetchall()
    return results


# 删除数据库表中重复的数据，（删除前将重复的数据备份到另外一个表中）
def deal_mysql_data():
    sql5 = 'delete from save_amount where No in (select No from (select No from save_amount where billNo in (select billNo from save_amount group by billNo having count(billNo)>1) and No not in (select min(No) from save_amount group by billNo having count(billNo)>1)) as temp);'
    try:
        cursor.execute(sql5)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()  # 事务提交
        #print('事务处理成功')


# 将根据对账单Excel中数据库查找的信息copy到一个新的excel中(不能循环)
def mysql_to_excel(xlsFile,newFile):
    a = 1
    amount = 0
    data = xlrd.open_workbook(xlsFile, formatting_info=True)
    table = data.sheets()[0]
    nrows = table.nrows
    new_excel = copy(data)
    ws = new_excel.get_sheet(0)  # 获取第一个sheet

    for i in range(1, nrows):
        billNo = table.cell_value(i, 1)
        cursor.execute('select billNo,amount from save_amount where billNo=%s', (billNo))
        results = cursor.fetchall()  # 获取多条数据
        # row_1 = cursor.fetchone()   # 获取一条数据
        if len(results)>0:  # 有客户对账单能够匹配的到
            amount = results[0][1]
            #print(a+1,billNo,amount)
        elif len(results)==0 and billNo!='':
            print(a + 1, billNo,'客户对账单无法匹配！！！')
            amount = 0
        ws.write(a, 13, billNo)    # 13列N
        ws.write(a, 14, amount)    # 14列O
        a = a + 1
    new_excel.save(newFile)

''''''''
def main():
    bill_file = 'F:\PythonProject\homework\\bill\\' + month +'.xls'  # 支付账单
    bill_copy = 'F:\PythonProject\homework\\bill\\' + month + 'copy.xls'  # 复制一份
    sample_file = 'F:\PythonProject\homework\sample'
    createTable()        # 创建一个空白数据库表
    #deleteTable()        # 删除备份表的数据
    sqlData = get_excel_data(bill_file)          # 获取对账单里面不需要的运单
    #print(sqlData)
    for i in range(len(sqlData)):
        billNo = sqlData[i][0]
        amount = sqlData[i][1]
        #print(i,'非对账单：',billNo,amount)
        data_to_mysql(billNo, amount)

    sqlDatas = get_excels_data(sample_file)      # 获取所有做账信息
    for i in range(len(sqlDatas)):
        billNo = sqlDatas[i][0]
        amount = sqlDatas[i][1]
        #print(billNo,amount)
        data_to_mysql(billNo, amount)

    #deal_mysql_data()

    mysql_to_excel(bill_file, bill_copy)

    db.close()
''''''''''''''


if __name__ == '__main__':
    #createTable()
    main()
    endtime = time.time() - start_time
    print('程序运行了%.2f秒' % endtime)